import time
import pymysql
import re
import datetime

# 在此处填写mysql密码
conn = pymysql.connect(host='localhost', user="root", passwd="", db="snmp", charset='utf8')
cur = conn.cursor()


# 连接数据库
def connectDatabase():
    try:
        # 在此处填写mysql密码
        conn = pymysql.connect(host='localhost', user="root", passwd="", db="snmp", charset='utf8')
    except:
        print("connectDatabase failed")
        return False
    cur = conn.cursor()
    # createTable()
    return True


# 关闭数据库
def close():
    # 如果数据打开，则关闭；否则没有操作
    if conn and cur:
        cur.close()
    conn.close()
    return True


# 执行数据库的sql语句,主要用来做插入操作
def execute(sql, params=None):
    # 连接数据库
    connectDatabase()
    try:
        if conn and cur:
            cur.execute(sql, params)
            conn.commit()
    except:
        print("execute failed: " + sql)
        print("params: " + params)
        close()
        return False
    return True


# 用来查询表数据
def fetchall(sql, params=None):
    execute(sql, params)
    return cur.fetchall()


# 创建数据表
def createTable():
    # 创建共享数据表
    sql = """CREATE TABLE IF NOT EXISTS `shared_data` (
                `time` datetime NOT NULL,  
                `ipInReceives` int NOT NULL,
                `ipOutRequests` int NOT NULL,
                `tcpInSegs` int NOT NULL,
                `tcpOutSegs` int NOT NULL,
                `udpInDatagrams` int NOT NULL,
                `udpOutDatagrams` int NOT NULL,
                PRIMARY KEY (`time`)
            ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0"""
    cur.execute(sql)
    # 创建端口数据表
    sql = """CREATE TABLE IF NOT EXISTS `port_data` (
                `time` datetime NOT NULL,  
                `port` int NOT NULL,
                `ifInOctets` int NOT NULL,
                `ifOutOctets` int NOT NULL,
                `ifInUcastPkts` int NOT NULL,
                `ifInNUcastPkts` int NOT NULL,
                `ifOutUcastPkts` int NOT NULL,
                `ifOutNUcastPkts` int NOT NULL,
                `ifInErrors` int NOT NULL,
                `ifOutErrors` int NOT NULL,
                `availability` double NOT NULL,
                `inErrorRate` double NOT NULL,
                `outErrorRate` double NOT NULL,
                PRIMARY KEY (`time`,`port`)
            ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0"""
    cur.execute(sql)


# 存储所有记录
def saveAll(res):
    sql = """INSERT INTO shared_data(time,ipInReceives,ipOutRequests,tcpInSegs,tcpOutSegs,udpInDatagrams,udpOutDatagrams)
            values (%s,%s,%s,%s,%s,%s,%s)"""
    print(type(res['ipInReceives']))
    cur.execute(sql, (res['time'], res['ipInReceives'],
                      res['ipOutRequests'], res['tcpInSegs'],
                      res['tcpOutSegs'], res['udpInDatagrams'],
                      res['udpOutDatagrams']))
    conn.commit()
    sql = """INSERT INTO port_data(time,port,ifInOctets,ifOutOctets,ifInUcastPkts,
            ifInNUcastPkts,ifOutUcastPkts,ifOutNUcastPkts,ifInErrors,ifOutErrors,
            availability,inErrorRate,outErrorRate)
            values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"""
    for i in range(0, len(res['portList'])):
        cur.execute(sql, (res['time'], res['portList'][i]['port'],
                          res['portList'][i]['ifInOctets'], res['portList'][i]['ifOutOctets'],
                          res['portList'][i]['ifInUcastPkts'], res['portList'][i]['ifInNUcastPkts'],
                          res['portList'][i]['ifOutUcastPkts'], res['portList'][i]['ifOutNUcastPkts'],
                          res['portList'][i]['ifInErrors'], res['portList'][i]['ifOutErrors'],
                          res['portList'][i]['availability'], res['portList'][i]['inErrorRate'],
                          res['portList'][i]['outErrorRate']))
        conn.commit()


# 查询函数，按照起止时间查询数据库中数据，按照和后端规定的json格式封装数据，并返回

def queryShare(startTime, endTime):
    # 获取shared_data表中数据
    sql = """select * from shared_data where time >= %s and time <= %s"""
    cur.execute(sql, (startTime, endTime))
    result = {}
    timeAxis = []
    ipInReceives = []
    ipOutRequests = []
    tcpInSegs = []
    tcpOutSegs = []
    udpInDatagrams = []
    udpOutDatagrams = []
    while 1:
        res = cur.fetchone()
        if res is None:
            # 表示已经取完结果集
            break
        tempTime = re.sub('[T]', " ", str(res[0]))
        timeAxis.append(tempTime)
        ipInReceives.append([tempTime, res[1]])
        ipOutRequests.append([tempTime, res[2]])
        tcpInSegs.append([tempTime, res[3]])
        tcpOutSegs.append([tempTime, res[4]])
        udpInDatagrams.append([tempTime, res[5]])
        udpOutDatagrams.append([tempTime, res[6]])
        # result.append({'time': res[0], 'ipInReceives': res[1], 'ipOutRequests': res[2],
        #                'tcpInSegs': res[3], 'tcpOutSegs': res[4], 'udpInDatagrams': res[5],
        #                'udpOutDatagrams': res[6]})
        # print(res[1])
    result['ipInReceives'] = ipInReceives
    result['ipOutRequests'] = ipOutRequests
    result['tcpInSegs'] = tcpInSegs
    result['tcpOutSegs'] = tcpOutSegs
    result['udpInDatagrams'] = udpInDatagrams
    result['udpOutDatagrams'] = udpOutDatagrams
    result['timeAxis'] = timeAxis

    # 表格所需数据，记录该时间段内各协议传输报文的变化量
    tableResult = {'changeIpIn': ipInReceives[-1][1] - ipInReceives[0][1],
                   'changeIpOut': ipOutRequests[-1][1] - ipOutRequests[0][1],
                   'changeTcpIn': tcpInSegs[-1][1] - tcpInSegs[0][1],
                   'changeTcpOut': tcpOutSegs[-1][1] - tcpOutSegs[0][1],
                   'changeUdpIn': udpInDatagrams[-1][1] - udpInDatagrams[0][1],
                   'changeUdpOut': udpOutDatagrams[-1][1] - udpOutDatagrams[0][1]}

    return result, tableResult


# 获取端口数据
def queryPort(startTime, endTime, port):
    # 获取port_data表中数据
    sql = """select * from port_data where time >= %s and time <= %s and port = %s"""
    cur.execute(sql, (startTime, endTime, port))
    result = {}
    tableResult = {}
    timeAxis = []
    ifInOctets = []
    ifOutOctets = []
    ifInUcastPkts = []
    ifInNUcastPkts = []
    ifOutUcastPkts = []
    ifOutNUcastPkts = []
    ifInErrors = []
    ifOutErrors = []
    availability = []
    inErrorRate = []
    outErrorRate = []

    # 平均利用率
    avgAbility = 0.00
    # 该时间段内信息条数
    count = 0

    while 1:
        res = cur.fetchone()
        print(res)
        if res is None:
            # 表示已经取完结果集
            break
        tempTime = re.sub('[T]', " ", str(res[0]))
        timeAxis.append(tempTime)
        ifInOctets.append([tempTime, res[2]])
        ifOutOctets.append([tempTime, res[3]])
        ifInUcastPkts.append([tempTime, res[4]])
        ifInNUcastPkts.append([tempTime, res[5]])
        ifOutUcastPkts.append([tempTime, res[6]])
        ifOutNUcastPkts.append([tempTime, res[7]])
        ifInErrors.append([tempTime, res[8]])
        ifOutErrors.append([tempTime, res[9]])
        availability.append([tempTime, res[10]])
        inErrorRate.append([tempTime, res[11]])
        outErrorRate.append([tempTime, res[12]])
        count = count + 1
        avgAbility = avgAbility + res[10]
        # result.append({'time': res[0], 'ipInReceives': res[1], 'ipOutRequests': res[2],
        #                'tcpInSegs': res[3], 'tcpOutSegs': res[4], 'udpInDatagrams': res[5],
        #                'udpOutDatagrams': res[6]})
        # print(res[1])
    result['ifInOctets'] = ifInOctets
    result['ifOutOctets'] = ifOutOctets
    result['ifInUcastPkts'] = ifInUcastPkts
    result['ifInNUcastPkts'] = ifInNUcastPkts
    result['ifOutUcastPkts'] = ifOutUcastPkts
    result['ifOutNUcastPkts'] = ifOutNUcastPkts
    result['ifInErrors'] = ifInErrors
    result['ifOutErrors'] = ifOutErrors
    result['availability'] = availability
    result['inErrorRate'] = inErrorRate
    result['outErrorRate'] = outErrorRate
    result['timeAxis'] = timeAxis

    changeInErrorRate = inErrorRate[-1][1] - inErrorRate[0][1]

    changeOutErrorRate = outErrorRate[-1][1] - outErrorRate[0][1]

    totalInPkts = ifInNUcastPkts[-1][1] + ifInUcastPkts[-1][1] - ifInNUcastPkts[0][1] - ifInUcastPkts[0][1]
    totalOutPkts = ifOutNUcastPkts[-1][1] + ifOutUcastPkts[-1][1] - ifOutNUcastPkts[0][1] - ifOutUcastPkts[0][1]
    if totalInPkts < 0 or totalOutPkts < 0:
        totalOutPkts = 0
        totalInPkts = 0
    if count != 0:
        avgAbility = round(avgAbility / count, 2)
    else:
        avgAbility = 0.00
    tableResult['interface'] = port
    tableResult['avgAva'] = '{:.2%}'.format(avgAbility)
    tableResult['ifTotalInPkts'] = totalInPkts
    tableResult['ifTotalOutPkts'] = totalOutPkts
    tableResult['changeInErrorRate'] = '{:.2%}'.format(changeInErrorRate)
    tableResult['changeOutErrorRate'] = '{:.2%}'.format(changeOutErrorRate)
    print(count)

    return result, tableResult


queryShare("2021-06-19 16:56:06", "2021-06-19 16:56:56")
queryPort("2021-06-19 16:55:51", "2021-06-19 16:56:01", 1)
# # 打开数据库连接
# conn = pymysql.connect(host='localhost', user="root", passwd="12345678", db="snmp")
# # 获取游标
# cur = conn.cursor()
#
# # 创建数据表
# # 创建共享数据表
# sql = """CREATE TABLE IF NOT EXISTS `shared_data` (
#       `time` datetime NOT NULL,
#       `ipInReceives` int NOT NULL,
#       `ipOutRequests` int NOT NULL,
#       `tcpInSegs` int NOT NULL,
#       `tcpOutSegs` int NOT NULL,
#       `udpInDatagrams` int NOT NULL,
#       `udpOutDatagrams` int NOT NULL,
#       PRIMARY KEY (`time`)
#     ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0"""
# cur.execute(sql)
# # 创建端口数据表
# sql = """CREATE TABLE IF NOT EXISTS `port_data` (
#       `time` datetime NOT NULL,
#       `port` int NOT NULL,
#       `ifInOctets` int NOT NULL,
#       `ifOutOctets` int NOT NULL,
#       `ifInUcastPkts` int NOT NULL,
#       `ifInNUcastPkts` int NOT NULL,
#       `ifOutUcastPkts` int NOT NULL,
#       `ifOutNUcastPkts` int NOT NULL,
#       `ifInErrors` int NOT NULL,
#       `ifOutErrors` int NOT NULL,
#       `availability` double NOT NULL,
#       `inErrorRate` int NOT NULL,
#       `outErrorRate` int NOT NULL,
#       PRIMARY KEY (`time`,`port`)
#     ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0"""
# cur.execute(sql)
# # cur.execute("select * from user;")
# while 1:
#     res = cur.fetchone()
#     if res is None:
#         # 表示已经取完结果集
#         break
#     print(res)
# cur.close()
# conn.commit()
# conn.close()
# print('sql执行成功')
